package com.sdu.cloudservice.dao;

import com.sdu.cloudservice.entity.UserEntity;
import org.apache.ibatis.annotations.*;
import org.springframework.stereotype.Repository;

import java.sql.Date;
import java.util.List;

/***
 @author
 ZMX,LXF
 */
@Mapper
@Repository
public interface UserDao {
    @Select("SELECT * FROM user WHERE username=#{username}")
    UserEntity findUserByUsername(@Param("username") String username);

    @Insert("INSERT INTO user(username,password,register_date,last_login,pic_download,excel_download,query_times) " +
            "VALUES(#{username},#{password},#{register_data},#{last_login},0,0,0)")
    int insertUser(@Param("username") String username,@Param("password") String password,
                   @Param("register_data") Date register_date,@Param("last_login") Date last_login);

    @Update("UPDATE user SET last_login =#{last_login} WHERE  username= #{username} ")
    int update(@Param("last_login")Date last_login,@Param("username") String username);

    @Update("UPDATE user SET password=#{password} WHERE username=#{username}")
    int update1(@Param("username")String username,@Param("password")String password);

    /**
     * 分页查询、排序
     * @param start     起始位置
     * @param number     查询数量：onepage
     * @return
     */
    @Select("SELECT * FROM user ORDER BY last_login desc,username asc limit #{start},#{number}")
    List<UserEntity> ListUserByLastLogin(@Param("start") int start,@Param("number") int number);

    /**
     * 根据注册时间降序
     * @param start
     * @param number
     * @return
     */
    @Select("SELECT * FROM user ORDER BY register_date desc,username asc limit #{start},#{number}")
    List<UserEntity> ListUserByRegisterDate(@Param("start") int start,@Param("number") int number);

    /**
     * 根据用户名升序
     * @param start
     * @param number
     * @return
     */
    @Select("SELECT * FROM user ORDER BY username asc limit #{start},#{number}")
    List<UserEntity> ListUserByUsername(@Param("start") int start,@Param("number") int number);

    /**
     * 根据excel下载次数倒序
     * @param start
     * @param number
     * @return
     */
    @Select("SELECT * FROM user ORDER BY excel_download desc,username asc limit #{start},#{number}")
    List<UserEntity> listUserByExcelDownload(@Param("start") int start,@Param("number") int number);

    /**
     * 根据图片下载次数倒序
     * @param start
     * @param number
     * @return
     */
    @Select("SELECT * FROM user ORDER BY pic_download desc,username asc limit #{start},#{number}")
    List<UserEntity> listUserByPicDownload(@Param("start") int start,@Param("number") int number);

    /**
     * 根据查询次数倒序
     * @param start
     * @param number
     * @return
     */
    @Select("SELECT * FROM user ORDER BY query_times desc,username asc limit #{start},#{number}")
    List<UserEntity> listUserByQueryTimes(@Param("start") int start,@Param("number") int number);

    /**
     * user数量
     * @return
     */
    @Select("SELECT COUNT(username) from user")
    int getNumOfUser();

    /**
     * 更新用户图片下载次数
     * @param username 用户名
     */
    @Update("UPDATE user SET pic_download=pic_download+1 WHERE username=#{username}")
    public void addPicDownload(@Param("username")String username);
    /**
     * 更新用户excel下载次数
     * @param username 用户名
     */
    @Update("UPDATE user SET excel_download=excel_download+1 WHERE username=#{username}")
    public void addExcelDownload(@Param("username")String username);
    /**
     * 更新用户查询次数
     * @param username 用户名
     */
    @Update("UPDATE user SET query_times=query_times+1 WHERE username=#{username}")
    public void addQueryTimes(@Param("username")String username);
}
